from flask import jsonify

from apps import dbUtil
from apps.command.commandService import get_trailer_command
from apps.gpsAndPathUtils import deal_cors
from apps.trailer.trailer import Trailer


# 更新拖车状态
def update_trailer_status_service(data):
    trailer = Trailer(data['trailer_ID'], data['status'])
    trailer.trailer_ID = trailer.trailer_ID.replace('"', "")
    print(trailer.getTrailerID(),trailer.getTrailerState())
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    print('UPDATE trailer SET state=' + str(
        trailer.getTrailerState()) + '  WHERE trailer_ID = "' + trailer.getTrailerID() + '";')

    affected_row = cursor.execute('UPDATE trailer SET state=' + str(trailer.getTrailerState()) + '  WHERE trailer_ID = "' + trailer.getTrailerID() + '";')
    db.commit()
    if affected_row == 1:
        return jsonify({"code": 200, "message": 'success'})
    elif affected_row == 0:
        return deal_cors(jsonify({"code": 500, "message": 'no trailer use this id or the trailer state is already this'}))


# 模糊查询获取拖车状态
def get_trailer_status_service(trailer_ID):
    print(trailer_ID)
    if trailer_ID is None:
        trailer_ID = ""
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM trailer WHERE trailer_ID LIKE "%' + str(trailer_ID) + '%";')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    list = []
    for record in data:
        from apps.path.pathService import getTrailerStartTimeAndTotalTime
        start_time, total_time = getTrailerStartTimeAndTotalTime(record[0])
        list.append({"trailer_ID": record[0], "state": record[1], "start_time": start_time, "total_time": total_time, "command": get_trailer_command(record[0])})
    return deal_cors(jsonify({"code": 200, "data": list}))


# 模糊查询获取拖车状态
def get_trailer_name_service(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()
    print("trailer_ID=", trailer_ID)
    trailer_ID = trailer_ID.replace('"', "")
    print('SELECT * FROM trailer WHERE trailer_ID LIKE "%' + trailer_ID + '%";')

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM trailer WHERE trailer_ID LIKE "%' + trailer_ID + '%";')
    # print('SELECT * FROM trailer WHERE trailer_ID LIKE "%' + trailer_ID + '%";')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()
    print("data = ", data)
    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    list = []
    for record in data:
        list.append(record[0])
    return deal_cors(jsonify({"code": 200, "data": list}))


# 精确获取拖车状态
def get_trailer_service(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM trailer WHERE trailer_ID = "' + str(trailer_ID) + '";')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    list = []
    for record in data:
        from apps.path.pathService import getTrailerStartTimeAndTotalTime
        start_time, total_time = getTrailerStartTimeAndTotalTime(record[0])
        list.append({"trailer_ID": record[0], "state": record[1], "start_time": start_time, "total_time": total_time,
                     "command": get_trailer_command(record[0])})

    return deal_cors(jsonify({"code": 200, "data": list}))

# 心跳线程 收GPS、拖车ID、状态码
def contract_with_heartbeat_service(data):
    gps1, gps2, trailer_ID, state = data['gps1'], data['gps2'], data['trailer_ID'].replace('"', ""), data['status']
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    # print('UPDATE trailer set state = '+str(state)+' where trailer_ID = "'+str(trailer_ID)+'";')
    cursor.execute('UPDATE trailer set state = '+str(state)+', cur_gps1 = "'+str(gps1)+'",cur_gps2 = "'+str(gps2)+'" where trailer_ID = "'+str(trailer_ID)+'";')
    db.commit()
    cursor.close()
    # 关闭游标和数据库的连接
    cursor.close()
    db.close()


    return deal_cors(jsonify({"code": 200, "data": "OK"}))


# 从数据库获取所有拖车的ID
def get_all_trailer_id():
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    cursor.execute('select trailer_ID from trailer')
    trailer_ids = cursor.fetchall()
    cursor.close()
    db.close()
    # 将从数据库获取的内容转换成list，然后再返回
    return [obj[0] for obj in trailer_ids]


# 根据id修改拖车状态
def modify_state_by_trailer_id(trailer_id, state):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    cursor.execute('update trailer set state="{}" where trailer_ID="{}"'.format(state, trailer_id))
    print('update trailer set state="{}" where trailer_ID="{}"'.format(state, trailer_id))
    db.commit()
    cursor.close()
    db.close()


# 根据trailer_id获取其状态
def get_state_by_trailer_id(trailer_id):
    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    cursor.execute('select state from trailer where trailer_ID="{}"'.format(trailer_id))
    ret = cursor.fetchall()
    cursor.close()
    db.close()
    return int(ret[0][0])


if __name__ == '__main__':
    # print(get_all_trailer_id())
    # modify_state_by_trailer_id("trailer001", 6)
    print(get_state_by_trailer_id("trailer001"))
